﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using DTO;

namespace DAO
{
    public class LichDiDAO
    {
        #region SelectLichDiByMaChuyen
        public static List<LichDiDTO> SelectLichDiByMaChuyen(String machuyen)
        {
            List<LichDiDTO> list = new List<LichDiDTO>();
            try
            {
                // Create List Sql Parameter
                List<SqlParameter> sqlParams = new List<SqlParameter>();
                sqlParams.Add(new SqlParameter("@machuyen", machuyen));
                DataTable dt = DataProvider.ExecuteQuery("sp_SelectLichDiByMaChuyen", sqlParams);
                foreach (DataRow dr in dt.Rows)
                {
                    LichDiDTO ld = new LichDiDTO();
                    ld.MACHUYEN = dr["MACHUYEN"].ToString();
                    ld.NGAYDI = DateTime.Parse(dr["ngaydi"].ToString());
                    ld.SOHIEU = dr["sohieu"].ToString();
                    ld.MALOAI = dr["MALOAI"].ToString();
                    ld.SOTAI = int.Parse(dr["sotai"].ToString());
                    ld.SOLO = int.Parse(dr["solo"].ToString());
                    ld.TTHIENTAI = int.Parse(dr["TTHIENTAI"].ToString());
                    ld.TINHTRANG = dr["TINHTRANG"].ToString();
                    list.Add(ld);
                }
            }
            catch (Exception ex)
            {
               // throw ex;
            }
            return list;
        }
        #endregion
        #region SelectALLLichDi
        public static List<LichDiDTO> SelectALLLichDi()
        {
            
            List<LichDiDTO> list = new List<LichDiDTO>();
                try
                {
                    DataTable dt = DataProvider.ExecuteQuery("sp_SelectALLLichDi");
                    foreach (DataRow dr in dt.Rows)
                    {
                         LichDiDTO ld = new LichDiDTO();
                        ld.MACHUYEN = dr["MACHUYEN"].ToString();
                        ld.NGAYDI = DateTime.Parse(dr["ngaydi"].ToString());
                        ld.SOHIEU = dr["sohieu"].ToString();
                        ld.MALOAI = dr["MALOAI"].ToString();
                        ld.SOTAI = int.Parse(dr["sotai"].ToString());
                        ld.SOLO = int.Parse(dr["solo"].ToString());
                        ld.TTHIENTAI = int.Parse(dr["TTHIENTAI"].ToString());
                        ld.TINHTRANG = dr["TINHTRANG"].ToString();
                        list.Add(ld);
                    }
                }
                catch (Exception ex)
                {
                    throw ex;
                }
                return list;
            }
        #endregion
        #region SelectLichDiByMaLoai
        public static List<LichDiDTO> SelectLichDiByMaLoai(String maloai)
        {
            List<LichDiDTO> list = new List<LichDiDTO>();
            try
            {
                // Create List Sql Parameter
                List<SqlParameter> sqlParams = new List<SqlParameter>();
                sqlParams.Add(new SqlParameter("@MALOAI", maloai));
                DataTable dt = DataProvider.ExecuteQuery("sp_SelectLichDiByMaLoai", sqlParams);
                foreach (DataRow dr in dt.Rows)
                {
                    LichDiDTO ld = new LichDiDTO();
                    ld.MACHUYEN = dr["MACHUYEN"].ToString();
                    ld.NGAYDI = DateTime.Parse(dr["ngaydi"].ToString());
                    ld.SOHIEU = dr["sohieu"].ToString();
                    ld.MALOAI = dr["MALOAI"].ToString();
                    ld.SOTAI = int.Parse(dr["sotai"].ToString());
                    ld.SOLO = int.Parse(dr["solo"].ToString());
                    ld.TTHIENTAI = int.Parse(dr["TTHIENTAI"].ToString());
                    ld.TINHTRANG = dr["TINHTRANG"].ToString();
                    list.Add(ld);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return list;
        }
        #endregion
        #region SelectLichDi
        public static List<LichDiDTO> SelectLichDi(DateTime NgayDi, String MaChuyen, String SoHieu)
        {
            List<LichDiDTO> list = new List<LichDiDTO>();
            try
            {
                // Create List Sql Parameter
                List<SqlParameter> sqlParams = new List<SqlParameter>();
                sqlParams.Add(new SqlParameter("@ngayDi", NgayDi));
                sqlParams.Add(new SqlParameter("@maChuyen", MaChuyen));
                sqlParams.Add(new SqlParameter("@soHieu", SoHieu));
                DataTable dt = DataProvider.ExecuteQuery("sp_SelectLichDi", sqlParams);
                foreach (DataRow dr in dt.Rows)
                {
                    LichDiDTO ld = new LichDiDTO();
                    ld.MACHUYEN = dr["MACHUYEN"].ToString();
                    ld.NGAYDI = DateTime.Parse(dr["NGAYDI"].ToString());
                    ld.SOHIEU = dr["sohieu"].ToString();
                    ld.MALOAI = dr["MALOAI"].ToString();
                    ld.SOTAI = int.Parse(dr["sotai"].ToString());
                    ld.SOLO = int.Parse(dr["solo"].ToString());
                    ld.TTHIENTAI = int.Parse(dr["TTHIENTAI"].ToString());
                    ld.TINHTRANG = dr["TINHTRANG"].ToString();
                    list.Add(ld);
                }
            }
            catch (Exception ex)
            {
                //throw ex;
            }
            return list;
        }
        #endregion
        #region Tang/Giam Hang Hoa
        public static void thayDoiHangHoa(String maKH, String maChuyen, String ngayDi, int khoiLuong)
        {

            SqlTransaction tran;
            SqlConnection conn = DataProvider.ConnectDB();
            SqlCommand command = conn.CreateCommand(); //tao doi tuong command
            tran = conn.BeginTransaction(IsolationLevel.ReadCommitted, "CapNhatTrongTai");//start tran tren connection nay
            command.Connection = conn;
            command.Transaction = tran;

            command.CommandTimeout = 2000;
            // khach hang ton tai ?
            command.CommandText = "select * from KHACHHANG kh where kh.MAKH=@maKH";
            SqlParameter pa = new SqlParameter("@maKH", SqlDbType.VarChar);
            SqlParameter pa2 = new SqlParameter("@ngayDi", SqlDbType.VarChar);
            SqlParameter pa3 = new SqlParameter("@maChuyen", SqlDbType.VarChar);
            pa.Value = maKH;
            pa2.Value = ngayDi;
            pa3.Value = maChuyen;
            command.Parameters.Add(pa);
            SqlDataReader rd = command.ExecuteReader();
            if (rd.HasRows == false)
            {
                rd.Close();
                tran.Rollback();
                throw new Exception("Loi khong tim thay khach hang");
            }
            command.Parameters.Clear();
            rd.Dispose();
            command.Dispose();
            // khach hang co dat cho ?
            command.CommandText = "select * from KHACHHANG  join DATCHO on KHACHHANG.MAKH=DATCHO.MAKH and KHACHHANG.MAKH=@maKH join LICHDI on LICHDI.NGAYDI=DATCHO.NGAYDI and LICHDI.MACHUYEN=DATCHO.MACHUYEN and LICHDI.MACHUYEN =@maChuyen and CONVERT(varchar(20),LICHDI.NGAYDI,103)=@ngayDi";
            command.Parameters.Add(pa);
            command.Parameters.Add(pa3);
            command.Parameters.Add(pa2);
            rd = command.ExecuteReader();
            if (rd.HasRows == false)
            {
                rd.Close();
                tran.Rollback();
                throw new Exception("Khách hàng này chưa đăng ký vận chuyển vào chuyến xe này");
            }
            rd.Close();
            command.Dispose();
            command.Parameters.Clear();
            //kiem tra trong tai sau khi cap nhat co am hay khong ?
            command.CommandText = "select TTHIENTAI from LichDi where MACHUYEN=@maChuyen and CONVERT(varchar(20),LICHDI.NGAYDI,103)=@ngayDi ";
            command.Parameters.Add(pa);
            command.Parameters.Add(pa3);
            command.Parameters.Add(pa2);
            int tt = (int)command.ExecuteScalar();//gia tri output
            //if (tt - khoiLuong < 0)
            //{
            //    rd.Close();
            //    tran.Rollback();
            //    throw new Exception("Trọng tải xe âm");
            //}
            rd.Close();
            command.Dispose();
            command.Parameters.Clear();
            System.Threading.Thread.Sleep(2000);
            tt = tt + khoiLuong;
            command.CommandText = "update LICHDI set TTHIENTAI=@tt where MACHUYEN=@maChuyen and CONVERT(varchar(20),LICHDI.NGAYDI,103)=@ngayDi";
            command.Parameters.Add(pa);
            command.Parameters.Add(pa3);
            command.Parameters.Add(pa2);
            SqlParameter pa4 = new SqlParameter("@tt", SqlDbType.Int);
            pa4.Value = tt;
            command.Parameters.Add(pa4);
            command.ExecuteNonQuery();

            tran.Commit();

            conn.Close();
            conn.Dispose();

        }
        #endregion
        #region usp_Soluongchuyen
        public static List<LichDiDTO> SelectSoLuongChuyen(String machuyen, String bd, String kt, ref int tong)
        {
            List<LichDiDTO> lst = new List<LichDiDTO>();

            SqlTransaction tran;
            SqlConnection conn = DataProvider.ConnectDB();
            SqlCommand command = conn.CreateCommand(); //tao doi tuong command
            tran = conn.BeginTransaction(IsolationLevel.ReadCommitted, "lichdishow");//start tran tren connection nay
            command.Connection = conn;
            command.Transaction = tran;

            command.CommandTimeout = 2000;
            //ma chuyen ton tai?
            command.CommandText = "select * from LICHDI where MACHUYEN=@ma";
            SqlParameter pa = new SqlParameter("@ma", SqlDbType.VarChar);
            SqlParameter pa2 = new SqlParameter("@ngaybd", SqlDbType.VarChar);
            SqlParameter pa3 = new SqlParameter("@ngaykt", SqlDbType.VarChar);
            pa.Value = machuyen;
            pa2.Value = bd;
            pa3.Value =kt;
            command.Parameters.Add(pa);
            SqlDataReader rd = command.ExecuteReader();
            if (rd.HasRows == false)
            {
                rd.Close();
                tran.Rollback();
                throw new Exception("Ma Chuyen Ko Ton Tai");
            }
            command.Parameters.Clear();
            rd.Dispose();
            command.Dispose();
                       //kiem tra trong tai sau khi cap nhat co am hay khong ?
            command.CommandText = "select count(MACHUYEN) from LICHDI where MACHUYEN=@ma and NGAYDI between @ngaybd and @ngaykt ";
            command.Parameters.Add(pa);
            command.Parameters.Add(pa3);
            command.Parameters.Add(pa2);
            int tt = (int)command.ExecuteScalar();//gia tri output
            tong = tt;
            System.Threading.Thread.Sleep(8000);
            rd.Dispose();
            command.Dispose();
            command.Parameters.Clear();
            command.CommandText = " select * from LICHDI where MACHUYEN=@ma and NGAYDI between @ngaybd and @ngaykt ";
            command.Parameters.Add(pa);
            command.Parameters.Add(pa3);
            command.Parameters.Add(pa2);
            SqlDataReader sa = command.ExecuteReader();
            while (sa.Read())
            {
                LichDiDTO ld = new LichDiDTO();
                ld.MACHUYEN = sa["MACHUYEN"].ToString();
                ld.NGAYDI = DateTime.Parse(sa["ngaydi"].ToString());
                ld.SOHIEU = sa["sohieu"].ToString();
                ld.MALOAI = sa["MALOAI"].ToString();
                ld.SOTAI = int.Parse(sa["sotai"].ToString());
                ld.SOLO = int.Parse(sa["solo"].ToString());
                ld.TTHIENTAI = int.Parse(sa["TTHIENTAI"].ToString());
                ld.TINHTRANG = sa["TINHTRANG"].ToString();
                lst.Add(ld);
            }
            sa.Dispose();
            tran.Commit();
            command.Dispose();
            conn.Close();
            conn.Dispose();
            return lst;

        }
        #endregion
        #region XoaLichDi
            public static bool XoaLichDi(String NgayDi,String MaChuyen,String SoHieu)
            {
                
                SqlParameter pa0 = new SqlParameter("@ngayDi", SqlDbType.VarChar);
                pa0.Value = NgayDi;
                SqlParameter pa1 = new SqlParameter("@maChuyen", SqlDbType.VarChar);
                pa1.Value = MaChuyen;
                SqlParameter pa2 = new SqlParameter("@soHieu", SqlDbType.VarChar);
                pa2.Value = SoHieu;

                SqlConnection conn = DataProvider.ConnectDB();
                SqlCommand cmd = conn.CreateCommand();
                cmd.Connection = conn;

                SqlTransaction tran = conn.BeginTransaction(IsolationLevel.ReadCommitted, "AAA");
                cmd.Transaction = tran;
                cmd.CommandText = "DELETE FROM DangKy Where NgayDi = @ngayDi AND MaChuyen = @maChuyen AND SoHieu = @soHieu";
                cmd.Parameters.Add(pa0);
                cmd.Parameters.Add(pa1);
                cmd.Parameters.Add(pa2);
                cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();

                cmd.CommandText = "DELETE FROM DatCho Where NgayDi = @ngayDi AND MaChuyen = @maChuyen AND SoHieu = @soHieu";
                cmd.Parameters.Add(pa0);
                cmd.Parameters.Add(pa1);
                cmd.Parameters.Add(pa2);
                cmd.ExecuteNonQuery();

                cmd.Parameters.Clear();
                cmd.CommandText = "DELETE FROM LichDi Where NgayDi = @ngayDi AND MaChuyen = @maChuyen AND SoHieu = @soHieu";
                cmd.Parameters.Add(pa0);
                cmd.Parameters.Add(pa1);
                cmd.Parameters.Add(pa2);
                cmd.ExecuteNonQuery();

                cmd.Dispose();
                tran.Commit();

                conn.Close();
                return true;
            }
        #endregion
        #region ThemLichDi
            public static bool ThemLichDi(String ngaydi, String machuyen, String sohieu, String maloai, int sotai, int solo, int tthientai, String tinhtrang)
            {

                #region parameter
                SqlParameter pa0 = new SqlParameter("@ngaydi", SqlDbType.VarChar);
                pa0.Value = ngaydi;
                SqlParameter pa1 = new SqlParameter("@machuyen", SqlDbType.VarChar);
                pa1.Value = machuyen;
                SqlParameter pa2 = new SqlParameter("@sohieu", SqlDbType.VarChar);
                pa2.Value = sohieu;
                SqlParameter pa3 = new SqlParameter("@maloai", SqlDbType.VarChar);
                pa3.Value = maloai;
                SqlParameter pa4 = new SqlParameter("@sotai", SqlDbType.Int);
                pa4.Value = sotai;
                SqlParameter pa5 = new SqlParameter("@solo", SqlDbType.Int);
                pa5.Value = solo;
                SqlParameter pa6 = new SqlParameter("@tthientai", SqlDbType.Int);
                pa6.Value = tthientai;
                SqlParameter pa7 = new SqlParameter("@tinhtrang", SqlDbType.VarChar);
                pa7.Value = tinhtrang;
                #endregion
                SqlConnection conn = DataProvider.ConnectDB();
                SqlCommand cmd = conn.CreateCommand();
                cmd.Connection = conn;
                #region begin transaction
                SqlTransaction tran = conn.BeginTransaction(IsolationLevel.ReadCommitted, "aa2");
                cmd.Transaction = tran;
                cmd.CommandText = "Select * from LICHDI where MACHUYEN=@machuyen AND SOHIEU=@sohieu AND @maloai=MALOAI and NGAYDI=@ngaydi";
                cmd.Parameters.Add(pa1);
                cmd.Parameters.Add(pa0);
                cmd.Parameters.Add(pa2);
                cmd.Parameters.Add(pa3);
                SqlDataReader dr = cmd.ExecuteReader();
                if (dr.HasRows)
                {
                    dr.Close();
                    cmd.Parameters.Clear();
                    tran.Rollback();
                    throw new Exception("Lich đi Da Co");
                }
                dr.Close();
                
                cmd.Parameters.Clear();
                cmd.CommandText = "insert into LICHDI values(@ngaydi,@maChuyen,@sohieu,@maloai,@sotai,@solo,@tthientai,@tinhtrang)";
                cmd.Parameters.Add(pa0);
                cmd.Parameters.Add(pa1);
                cmd.Parameters.Add(pa2);
                cmd.Parameters.Add(pa3);
                cmd.Parameters.Add(pa4);
                cmd.Parameters.Add(pa5);
                cmd.Parameters.Add(pa6);
                cmd.Parameters.Add(pa7);
                cmd.ExecuteNonQuery();
                cmd.Dispose();
                tran.Commit();
                #endregion
                conn.Close();
                return true;
            }
        #endregion
        #region SuaLichDi
            public static bool SuaLichDi(String ngaydi, String machuyen, String sohieu, String maloai, int sotai, int solo, int tthientai, String tinhtrang)
            {
                #region parameter
                SqlParameter pa0 = new SqlParameter("@ngaydi", SqlDbType.VarChar);
                pa0.Value = ngaydi;
                SqlParameter pa1 = new SqlParameter("@machuyen", SqlDbType.VarChar);
                pa1.Value = machuyen;
                SqlParameter pa2 = new SqlParameter("@sohieu", SqlDbType.VarChar);
                pa2.Value = sohieu;
                SqlParameter pa3 = new SqlParameter("@maloai", SqlDbType.VarChar);
                pa3.Value = maloai;
                SqlParameter pa4 = new SqlParameter("@sotai", SqlDbType.Int);
                pa4.Value = sotai;
                SqlParameter pa5 = new SqlParameter("@solo", SqlDbType.Int);
                pa5.Value = solo;
                SqlParameter pa6 = new SqlParameter("@tthientai", SqlDbType.Int);
                pa6.Value = tthientai;
                SqlParameter pa7 = new SqlParameter("@tinhtrang", SqlDbType.VarChar);
                pa7.Value = tinhtrang;
                #endregion
                SqlConnection conn = DataProvider.ConnectDB();
                SqlCommand cmd = conn.CreateCommand();
                cmd.Connection = conn;
                #region begin transaction
                SqlTransaction tran = conn.BeginTransaction(IsolationLevel.ReadCommitted, "aa2");
                cmd.Transaction = tran;
                cmd.CommandText = "Select * from LICHDI where MACHUYEN=@machuyen AND SOHIEU=@sohieu AND @maloai=MALOAI and NGAYDI=@ngaydi";
                cmd.Parameters.Add(pa1);
                cmd.Parameters.Add(pa0);
                cmd.Parameters.Add(pa2);
                cmd.Parameters.Add(pa3);
                SqlDataReader dr = cmd.ExecuteReader();
                if (!dr.HasRows)
                {
                    dr.Close();
                    cmd.Parameters.Clear();
                    tran.Rollback();
                    throw new Exception("Lich đi Da Co");
                }
                dr.Close();

                cmd.Parameters.Clear();
                cmd.CommandText = "update LICHDI set MALOAI=@maloai,SOTAI=@sotai, SOLO = @solo where MACHUYEN=@machuyen AND SOHIEU=@sohieu AND @maloai=MALOAI and NGAYDI=@ngaydi";
                cmd.Parameters.Add(pa0);
                cmd.Parameters.Add(pa1);
                cmd.Parameters.Add(pa2);
                cmd.Parameters.Add(pa3);
                cmd.Parameters.Add(pa4);
                cmd.Parameters.Add(pa5);
                cmd.Parameters.Add(pa6);
                cmd.Parameters.Add(pa7);
                cmd.ExecuteNonQuery();
                cmd.Dispose();
                tran.Commit();
                #endregion
                conn.Close();
                return true;
            }
        #endregion
        public static LichDiDTO SelectLichDiByPrimaryKey(String ngayDi, String maChuyen, String soHieu)
            {
                LichDiDTO ld = new LichDiDTO();
                SqlConnection conn = DataProvider.ConnectDB();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = conn;
                cmd.CommandText = "select * from LichDi where CONVERT(varchar(20),LICHDI.NGAYDI,103)=@ngayDi and MaChuyen=@maChuyen and SoHieu=@soHieu";
                SqlParameter pa = new SqlParameter("@ngayDi", SqlDbType.VarChar);
                pa.Value = ngayDi;
                cmd.Parameters.Add(pa);
                pa = new SqlParameter("@maChuyen", SqlDbType.VarChar);
                pa.Value = maChuyen;
                cmd.Parameters.Add(pa);

                pa = new SqlParameter("@soHieu", SqlDbType.VarChar);
                pa.Value = soHieu;
                cmd.Parameters.Add(pa);

                SqlDataReader dr = cmd.ExecuteReader();
                if (dr.Read())
                {
                    ld.NGAYDI = dr.GetDateTime(0);
                    ld.MACHUYEN = dr.GetString(1);
                    ld.SOHIEU = dr.GetString(2);
                    ld.MALOAI = dr.GetString(3);
                    ld.SOTAI = dr.GetInt32(4);
                    ld.SOLO = dr.GetInt32(5);
                    ld.TTHIENTAI = dr.GetInt32(6);
                    ld.TINHTRANG = dr.GetString(7);
                }
                cmd.Dispose();
                conn.Close();
                return ld;
            }
    }
}
